package cn.telling.sellercentre.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import cn.telling.common.groupBuy.GroupBuyStatus;
import cn.telling.common.pager.PageVo;
import cn.telling.common.pager.Pager;
import cn.telling.common.util.TellingConstance;
import cn.telling.product.vo.ShopProductVo;
import cn.telling.sellerCenter.vo.ShopProductApproveVo;
import cn.telling.sellerCenter.vo.ShopProductAreaApproveVo;
import cn.telling.sellerCenter.vo.ShopProductAreaVo;
import cn.telling.sellercentre.dao.ISellerCentreDao;
import cn.telling.utils.BaseUtil;
import cn.telling.utils.StringHelperTools;

/**   
 * @Description: 产品管理   产品中心数据处理层实现
 */
@Repository("SellerCentreDao")
public class SellerCentreDaoImpl implements ISellerCentreDao{
	private static Logger logger = Logger.getLogger(SellerCentreDaoImpl.class);
	
	@Autowired
	@Qualifier("film-template")
	private JdbcTemplate jdbcTemplate;

	/**根据用户Id获得商铺简介的信息*/
	/*@Override
	public SupplyVo GetShopInfo(String userId) {
		String sql = "select introduction from supply where user_id = ?" ;
		SupplyVo supplyVo = null;
		List<SupplyVo> list = jdbcTemplate.query(sql,new Object[]{userId},new SupplyVoMapper());
		
		if(list != null && list.size() > 0){
			supplyVo = list.get(0);
		}
		return supplyVo;
	}*/

	/*private static final class SupplyVoMapper implements RowMapper<SupplyVo> {
		public SupplyVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			SupplyVo supplyVo = new SupplyVo();
			supplyVo.setINTRODUCTION(rs.getString("introduction"));		
			return supplyVo;
		}
	}*/
	
	/**根据用户Id修改商铺简介信息*/
	@Override
	public int SetShopIntroduce(String userId, String shopintroduce) {
		String sql = "update supply set introduction = ?,update_date = sysDate,update_user = ? where user_id = ?";
		int count = jdbcTemplate.update(sql, new Object[]{shopintroduce,userId,userId});
		return count;
	}
	
	/**根据用户Id获得供应商类型    空   外部供应商  01   天音供应商     02 03厂家供应商*/
	@Override
	public String GetSupplyType(String userId) {
		String sql = "select supply_type from supply where user_id = ?" ;
		Object[] params = new Object[]{ userId };
		params = StringHelperTools.nullToString(params);
		
		List<String> list = jdbcTemplate.query(sql, params, new RowMapper<String>(){
			public String mapRow(ResultSet rs,int rowNum) throws SQLException{
				String supplyType = rs.getString("supply_type");
				return supplyType;
			}
		});
		
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return "";
	}
	
	/**获得卖家中心已上架、已下架、审核中产品列表 */
	@Override
	public List<ShopProductAreaVo> getSaleListByStatus(String userId,String saleType, String status, String conditions) {
		String sql = "with productData as( " +
					 " select sp.id as shopProductId, sp.productId, pm.productName, sp.supplyId," +
					 " sa.id as supplyAreaId, sa.saleType, sa.areaId, ai.areaName, sa.customId, NVL(bi.companyName,bi.shopName) as companyName," +
					 " sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts," +
					 " sa.onShelfTime, sa.offShelfTime, sa.isShare, sa.shareCode" +
					 " from shop_product sp " +
					 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " inner join supply s on sp.supplyId = s.id and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
					 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " + 
					 " left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " left join areaInfo ai on sa.areaId = ai.areaId " +
					 " left join buyerInfo bi on sa.customId = bi.buyerId and bi.delflag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " where s.user_id = ? " +
					 ((!"".equals(status) && status != null) ? " and sa.status = '" + status + "' and sa.saleStatus <> '" + TellingConstance.SALESTATUS1 + "' " : " and (spa.approveStatus = '" + TellingConstance.SALESTATUS1 + "' or spa.approveStatus is null) ") +
					 ((!"".equals(saleType) && saleType != null) ? " and sa.saleType in ('" + saleType + "') " : "") + 
					 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
					 " and lower(replace(pm.productName, ' ', '')) like ? " +
					 " group by sp.id, sp.productId, pm.productName, sp.supplyId, sa.id, sa.saleType, sa.areaId, ai.areaName, sa.customId, NVL(bi.companyName,bi.shopName), sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime,sa.isShare, sa.shareCode " +
					 "), " +
					 "promotiondData as (" +
					 " select pdh.productId shopProductId, pdh.status, nvl(pdh.partiQuantity,0) - sum(nvl(od.product_num, 0)) promotiondNum " +
					 " from promotionm pmh " +
					 " inner join promotiond pdh on pmh.id = pdh.pId " +
					 " left join order_detail0 od  on od.active_id = pdh.id " 
//					 + " and od.order_type = '" + OrderConstantValues.ORDER_TYPE_02 
					 +"' and  od.order_status <> '05' and od.order_status <> '06' and od.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " where pmh.isChecked = '2' and pmh.protype = 2 and (pdh.status = " + GroupBuyStatus.waitPulish.index() + " or pdh.status = " + GroupBuyStatus.inProcess.index() +" or pdh.status = " + GroupBuyStatus.intime.index() + ") " +
					 " group by pdh.productId, pdh.status, pdh.partiQuantity " +
					 "), " +
					 "seckillData as (" +
					 " select sk.productId shopProductId, sk.statu, sum(nvl(sk.order_num,0)) seckillNum " + 
					 " from seckill sk " +
					 " where sk.statu = 1 or sk.statu = 3 " +
					 " group by sk.productId, sk.statu " +
					 ") " +
					 "select pd.shopProductId, pd.productId, pd.productName, pd.supplyId, pd.supplyAreaId," +
					 " pd.saleType, pd.areaId, pd.areaName, pd.customId, pd.companyName,"+
					 " pd.priceRetailOnline,(nvl(pd.stockNumber,0) + nvl(pdd.promotiondNum,0) + nvl(skd.seckillNum,0)) stockNumber, pd.afterSale, pd.gifts," +
					 " pd.onShelfTime, pd.offShelfTime, pd.isShare, pd.shareCode," +
					 " pdd.status promotiondStatus, nvl(pdd.promotiondNum,0) promotiondNum," +
					 " skd.statu seckillStatus, nvl(skd.seckillNum,0) seckillNum " +
					 "from productData pd " +
					 "left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +
					 "left join seckillData skd on skd.shopProductId = pd.shopProductId " +
					 "order by pd.productName";
		Object[] params = new Object[]{userId, "%" + conditions.replaceAll("'", "''").replaceAll("\\s*", "").toLowerCase() + "%"};
		params = StringHelperTools.nullToString(params);
		
		logger.debug("获得卖家中心产品列表" +BaseUtil.logSQL(sql, params));
		
		List<ShopProductAreaVo> shopProductAreaVoList = jdbcTemplate.query(sql, params, tempMapper);
		
		if(shopProductAreaVoList != null && shopProductAreaVoList.size() > 0){
			return shopProductAreaVoList;
		}
		return null;
	}
	
	/**数据转化*/
	private ParameterizedRowMapper<ShopProductAreaVo> tempMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setShopProductId(rs.getBigDecimal("shopProductId"));
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductAreaVo.setProductId(rs.getBigDecimal("productId"));
			shopProductAreaVo.setProductName(rs.getString("productName"));
			shopProductAreaVo.setSaleType(rs.getString("saleType"));
			shopProductAreaVo.setAreaId(rs.getLong("areaId"));
			shopProductAreaVo.setAreaName(rs.getString("areaName"));
			shopProductAreaVo.setCustomId(rs.getLong("customId"));
			shopProductAreaVo.setCustomName(rs.getString("companyName"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setAfterSale(rs.getString("afterSale"));
			shopProductAreaVo.setGifts(rs.getString("gifts"));
			shopProductAreaVo.setOnShelfTime(rs.getDate("onShelfTime"));
			shopProductAreaVo.setOffShelfTime(rs.getDate("offShelfTime"));
			shopProductAreaVo.setIsShare(rs.getString("isShare"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			shopProductAreaVo.setPromotiondStatus(rs.getString("promotiondStatus"));
			shopProductAreaVo.setPromotiondNum(rs.getBigDecimal("promotiondNum"));
			shopProductAreaVo.setSeckillStatus(rs.getString("seckillStatus"));
			shopProductAreaVo.setSeckillNum(rs.getBigDecimal("seckillNum"));
			return shopProductAreaVo;
		}
	};
	
	/**根据供货商产品信息提交产品审核申请*/
	@Override
	public int insertShopProductApprove(ShopProductAreaApproveVo shopProductAreaApproveVo) {
		String sql = "insert into shop_product_approve(" +
					 "id, supplyAreaId, priceBefore, priceAfter, stockBefore, stockAfter, saleStatus, " +
					 "approveStatus, expApproveTime, del_flag, add_date, add_user, remark) " +
					 "values(?, ?, ?, ?, ?, ?, ?, '1', SYSDATE + 1, '1', SYSDATE, ?, ?)";

		List<Object[]> paramList = new ArrayList<Object[]>();
		List<ShopProductAreaVo> shopProductAreaVoList = shopProductAreaApproveVo.getShopProductAreaVoList();
		for (int i = 0; i < shopProductAreaVoList.size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductAreaVoList.get(i);
			
			Object[] parmas = new Object[9];
			parmas[0] = shopProductAreaApproveVo.getId()[i];
			parmas[1] = shopProductAreaVo.getSupplyAreaId();
			parmas[2] = shopProductAreaVo.getPriceRetailOnline();
			parmas[3] = shopProductAreaVo.getApprovePrice();
			parmas[4] = shopProductAreaVo.getStockNumber();
			parmas[5] = shopProductAreaVo.getApproveStockNum();
			parmas[6] = shopProductAreaVo.getSaleStatus();
			parmas[7] = shopProductAreaVo.getAddUser();
			parmas[8] = shopProductAreaVo.getRemark();
			
			paramList.add(parmas);
			logger.debug("供货商产品信息提交产品审核申请" + BaseUtil.logSQL(sql, parmas));
		}
		
		int[] count = jdbcTemplate.batchUpdate(sql, paramList);
		return count.length;
	}
	
	/**根据货商产品信息提交产品审核申请 更新供应商产品区域销售状态*/
	@Override
	public int updateSupplyAreaSaleStatus(ShopProductAreaApproveVo shopProductAreaApproveVo) {
		String sql = "update supply_area0 set saleStatus = '1', update_user = ? where id = ?";
		
		List<Object[]> paramList = new ArrayList<Object[]>();
		List<ShopProductAreaVo> shopProductAreaVoList = shopProductAreaApproveVo.getShopProductAreaVoList();
		for (int i = 0; i < shopProductAreaVoList.size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductAreaVoList.get(i);
			Object[] parmas = new Object[2];
			parmas[0] = shopProductAreaVo.getAddUser();
			parmas[1] = shopProductAreaVo.getSupplyAreaId();
			paramList.add(parmas);
			
			logger.debug("更新供应商产品区域销售状态" + BaseUtil.logSQL(sql,  new Object[]{}));
		}
		
		int[] count = jdbcTemplate.batchUpdate(sql.toString(), paramList);
		return count.length;
	}
	
	/**根据共享编码查询出共享产品列表  供共享使用*/
	@Override
	public List<ShopProductAreaVo> getSupplyAreaByShareCode(String shareCode) {
		String sql = "select sa.id as supplyAreaId, sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.shareCode from supply_area0 sa where sa.shareCode = ?";
		Object[] params = new Object[]{shareCode};
		params = StringHelperTools.nullToString(params);
		
		logger.debug("根据共享编码查询出共享产品列表" + BaseUtil.logSQL(sql, params));
		List<ShopProductAreaVo> list = jdbcTemplate.query(sql, params, supplyAreaMapper);
		if(list != null && list.size() > 0){
			return list;
		}
		return null;
	}
	
	/**数据转化*/
	private ParameterizedRowMapper<ShopProductAreaVo> supplyAreaMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			return shopProductAreaVo;
		}
	};
	
	/**根据supplyAreaId 查询出供货商产品区域*/
	@Override
	public ShopProductAreaVo getSupplyAreaById(String supplyAreaId) {
		String sql = "select sa.id as supplyAreaId, sp.id as shopProductId, sp.productId, sp.supplyId, sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.saleType, sa.isShare, sa.shareCode " +
					 "from supply_area0 sa " + 
					 "left join shop_product sp on sa.supplyproductId = sp.id and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
					 "where sa.id = ? and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' ";
		Object[] params = new Object[]{ supplyAreaId };
		params = StringHelperTools.nullToString(params);
		
		logger.debug("根据supplyAreaId 查询出供货商产品区域" + BaseUtil.logSQL(sql, params));
		List<ShopProductAreaVo> list = jdbcTemplate.query(sql, params, rowMapper);
		
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	
	/**数据转换*/
	private ParameterizedRowMapper<ShopProductAreaVo> rowMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setShopProductId(rs.getBigDecimal("shopProductId"));
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductAreaVo.setProductId(rs.getBigDecimal("productId"));
			shopProductAreaVo.setSaleType(rs.getString("saleType"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setIsShare(rs.getString("isShare"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			return shopProductAreaVo;
		}
	};
	
	/**同一编码更新同一库存*/
	@Override
	public int updateShareStockNumber(String stockNumber, String shareCode) {
		String sql = "update supply_area0 set stockNumber = ?, overPlusNumber = ? where shareCode = ?";
		Object[] params = new Object[]{ stockNumber, stockNumber, shareCode };
		params = StringHelperTools.nullToString(params);
		
		logger.debug("同一编码更新同一库存 :" + BaseUtil.logSQL(sql, params));
		int count = jdbcTemplate.update(sql, params);
		
		return count;
	}
	
	/**根据用户Id、销售类型(定向、非定向)、上下架状态、型号名称   获得供货商产品总数*/
	@Override
	public int GetShopProductCount(String userId, String saleType, String status,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		String sql = " select count(1) " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" where a.id not in (select distinct bsp.supplyproductid from bidding_sp bsp " +
				" inner join biddingm bm on bm.id=bsp.pid and bm.statue in ('1','2') " +
				" where bsp.product_statu in ('1','2','3','4','6') and bsp.supplyproductid is not null) " + 
				" and d.user_id = '" + userId + "'" +
				" and EXISTS (select 1 from supply_area0 e where a.id = e.supplyProductId" +
				" and e.del_flag = '1' and e.isValId = '01' and e.status = '" + status + "' and e.saleStatus <> '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" ) " +
				" and a.del_flag = '1' and a.isValId = '01' ";
		
		if(modelName != null && !"".equals(modelName)){
			sql += " and pm.modelName like ? ";
			conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
		}
		
		Object[] params = conditionList.toArray();
		
		return jdbcTemplate.queryForInt(sql,params);
	}

	/**根据用户Id、销售类型(定向、非定向)、上下架状态、型号名称  分页获得供货商产品列表（分页）*/
	@Override
	public List<ShopProductVo> GetShopProductList(String userId, String saleType, String status, PageVo pageVo,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		
		String sql = " select a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1,max(e.saleType) as saleMode " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" inner join supply_area0 e on a.id = e.supplyProductId and e.del_flag = '1' and e.isValId = '01' " +
				" where a.id not in (select distinct bsp.supplyproductid from bidding_sp bsp "+
	            " inner join biddingm bm on bm.id=bsp.pid and bm.statue in ('1','2') "+
	            " where bsp.product_statu in ('1','2','3','4','6') and bsp.supplyproductid is not null) "+
	            " and d.user_id = '" + userId + "'" +
				" and e.status = '" + status + "' and e.saleStatus <> '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" and a.del_flag = '1' and a.isValId = '01' ";
				
				if(modelName != null && !"".equals(modelName)){
					sql += " and pm.modelName like ? ";
					conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
				}
				
				sql += " group by a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1 " +
					   " order by a.id desc ";
		
		String execSql = Pager.getPageDatas(pageVo, sql);
		Object[] params = conditionList.toArray();
		
		List<ShopProductVo> list = (List<ShopProductVo>)jdbcTemplate.query(execSql, params, new RowMapper<ShopProductVo>(){
			public ShopProductVo mapRow(ResultSet rs,int i) throws SQLException{
				ShopProductVo shopProductVo = new ShopProductVo();
				shopProductVo.setId(rs.getBigDecimal("id"));
				shopProductVo.setProductId(rs.getBigDecimal("productId"));
				shopProductVo.setShopId(rs.getString("shopId"));
				shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
				shopProductVo.setSaleMode(rs.getString("saleMode"));
				shopProductVo.setProductName(rs.getString("productName"));
				shopProductVo.setProductPic(rs.getString("picturePath1"));
				return shopProductVo;
			}
		});
		return list;
	}
	
	/**根据用户Id、销售类型(定向、非定向)、上下架状态   获得所有供货商产品列表（不分页）*/
	@Override
	public List<ShopProductVo> GetShopProductListNoPage(String userId, String saleType, String status) {
		String sql = " select a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1,max(e.saleType) as saleMode " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" inner join supply_area0 e on a.id = e.supplyProductId and e.del_flag = '1' and e.isValId = '01' " +
				" where d.user_id = '" + userId + "'" +
				" and e.status = '" + status + "' and e.saleStatus <> '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" and a.del_flag = '1' and a.isValId = '01' ";
				
				sql += " group by a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1 " +
					   " order by a.id desc ";
				
		return jdbcTemplate.query(sql, new ShopProductVoMapper());
	}
	
	/**根据用户Id、销售类型(定向、非定向)、型号名称   获得审核中的供货商产品总数*/
	@Override
	public int GetApproveShopProductCount(String userId, String saleType,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		
		String sql = " select count(1) " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" where d.user_id = '" + userId + "'" +
				" and EXISTS (select 1 from supply_area0 e where a.id = e.supplyProductId" +
				" and e.del_flag = '1' and e.isValId = '01' and e.saleStatus = '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" ) " +
				" and a.del_flag = '1' and a.isValId = '01' ";
		
		if(modelName != null && !"".equals(modelName)){
			sql += " and pm.modelName like ? ";
			conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
		}
		
		Object[] params = conditionList.toArray();
		return jdbcTemplate.queryForInt(sql,params);
	}

	/**根据用户Id、销售类型(定向、非定向)、型号名称  分页获得审核中的供货商产品列表（分页）*/
	@Override
	public List<ShopProductVo> GetApproveShopProductList(String userId, String saleType, PageVo pageVo,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		
		String sql = " select a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1,max(e.saleType) as saleMode " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" inner join supply_area0 e on a.id = e.supplyProductId and e.del_flag = '1' and e.isValId = '01' " +
				" where d.user_id = '" + userId + "'" +
				" and e.saleStatus = '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" and a.del_flag = '1' and a.isValId = '01' ";
				
				if(modelName != null && !"".equals(modelName)){
					sql += " and pm.modelName like ? ";
					conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
				}
				
				sql += " group by a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1 " +
					   " order by a.id desc ";
		
		String execSql = Pager.getPageDatas(pageVo, sql);
		Object[] params = conditionList.toArray();
		
		List<ShopProductVo> list = (List<ShopProductVo>)jdbcTemplate.query(execSql, params, new RowMapper<ShopProductVo>(){
			public ShopProductVo mapRow(ResultSet rs,int i) throws SQLException{
				ShopProductVo shopProductVo = new ShopProductVo();
				shopProductVo.setId(rs.getBigDecimal("id"));
				shopProductVo.setProductId(rs.getBigDecimal("productId"));
				shopProductVo.setShopId(rs.getString("shopId"));
				shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
				shopProductVo.setSaleMode(rs.getString("saleMode"));
				shopProductVo.setProductName(rs.getString("productName"));
				shopProductVo.setProductPic(rs.getString("picturePath1"));
				return shopProductVo;
			}
		});
		return list;
	}
	
	/**根据用户Id、销售类型(定向、非定向)  获得所有审核中的供货商产品列表（不分页）*/
	@Override
	public List<ShopProductVo> GetApproveShopProductListNoPage(String userId, String saleType) {
		String sql = " select a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1,max(e.saleType) as saleMode " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" inner join supply_area0 e on a.id = e.supplyProductId and e.del_flag = '1' and e.isValId = '01' " +
				" where d.user_id = '" + userId + "'" +
				" and e.saleStatus = '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" and a.del_flag = '1' and a.isValId = '01' " +
				" group by a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1 " +
				" order by a.id desc ";
		return jdbcTemplate.query(sql, new ShopProductVoMapper());
	}
	
	/**
	 * @Description:产品列表数据转换用
	 */
	private static final class ShopProductVoMapper implements RowMapper<ShopProductVo> {
		public ShopProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductVo shopProductVo = new ShopProductVo();
			shopProductVo.setId(rs.getBigDecimal("id"));
			shopProductVo.setProductId(rs.getBigDecimal("productId"));
			shopProductVo.setShopId(rs.getString("shopId"));
			shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductVo.setSaleMode(rs.getString("saleMode"));
			shopProductVo.setProductName(rs.getString("productName"));
			shopProductVo.setProductPic(rs.getString("picturePath1"));
			
			return shopProductVo;
		}
	}
	
	/**根据供货商产品Id、用户Id 获得供货商产品信息*/
	@Override
	public ShopProductVo GetShopProduct(String shopProductId, String userId) {
		String sql = " select sp.id, sp.productId,sp.shopId,'' as status,sp.supplyId,pm.productName,pp.picturePath1,RTRIM(sp.saleMode) as saleMode " +
				" from shop_product sp " +
				" inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '1' " +
				" left  join product_pic_relation ppr on pm.productId = ppr.product_id " +
				" left  join product_picture pp on ppr.pictureId = pp.pictureId and pp.del_flag = '1' " +
				" inner join supply s on sp.supplyId = s.id and s.del_flag = '1' " +
				" where sp.id = " + shopProductId + 
				" and sp.del_flag = '1' and sp.isValId = '01' " +
				" and s.user_id = '" + userId + "'" +
				" order by sp.id desc";
		List<ShopProductVo> shopProductVoList = jdbcTemplate.query(sql, new ShopProductVoMapper());
		if(shopProductVoList != null && shopProductVoList.size() > 0){
			return shopProductVoList.get(0);
		}
		return null;
	}
	
	/**根据供货商产品Id 获得供货商产品信息*/
	@Override
	public ShopProductVo GetShopProduct(String shopProductId) {
		String sql = " select sp.id, su.pri_mail, sp.productId,sp.shopId,'' as status,sp.supplyId,pm.productName,pp.picturePath1,RTRIM(sp.saleMode) as saleMode " +
				" from shop_product sp " +
				" inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '1' " +
				" left  join product_pic_relation ppr on pm.productId = ppr.product_id " +
				" left  join product_picture pp on ppr.pictureId = pp.pictureId and pp.del_flag = '1' " +
				" left  join supply su on su.id = sp.supplyid " +
				" where sp.id = " + shopProductId + 
				" and sp.del_flag = '1' and sp.isValId = '01' " +
				" order by sp.id desc";
		
		List<ShopProductVo> shopProductVoList = jdbcTemplate.query(sql, new ShopProductVoMapper());
		if(shopProductVoList != null && shopProductVoList.size() > 0){
			return shopProductVoList.get(0);
		}
		return null;
	}
	
	/**根据供货商产品Id、销售状态(1 审核中、2 审核未通过、3  审核通过)、上下架状态  获得该供货商产品的指定状态的发布信息*/
	@Override
	public List<ShopProductAreaVo> GetShopProductAreaList(String shopProductId, String saleStatus, String status) {
		
		String sql = " select sa.id, sa.areaId, ai.areaName, sa.isContinuous," +
				" sa.priceRetailOnline, spa.priceAfter, sa.overPlusNumber as stockNumber, spa.stockAfter," +
				" sa.customId, NVL(bi.companyName,bi.shopName) as companyName," +
				" sa.saleType, sa.status, spa.saleStatus," +
				" sa.onShelfTime, sa.offShelfTime, spa.expApproveTime, spa.approveTime," +
				" spa.approveUser,spa.remark," +
				" sa.isShare, sa.shareCode " +
				" from supply_area0 sa " +
				" left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag <> '0' and (spa.approveStatus = '1' or spa.approveStatus is null) " +
				" left join areaInfo ai on sa.areaId = ai.areaId" +
				" left join buyerInfo bi on sa.customId = bi.buyerId and bi.delflag <> '0' " +
				" where sa.supplyProductId = " + shopProductId +
				("0".equals(saleStatus) ? " and sa.saleStatus <> '1' and sa.status = '" + status + "' " : " and sa.saleStatus = '1' ") +
				" and sa.del_flag = '1' and sa.isValId = '01' " +
				" order by sa.id ";
		return jdbcTemplate.query(sql, new ShopProductAreaVoMapper());
	}
	
	/**根据供货商产品Id   获得该供货商产品的指定状态的发布信息*/
	@Override
	public List<ShopProductAreaVo> GetShopProductAreaList(String shopProductId) {
		
		String sql = " select sa.id, sa.areaId, ai.areaName, sa.isContinuous," +
				" sa.priceRetailOnline, spa.priceAfter, sa.overPlusNumber as stockNumber, spa.stockAfter," +
				" sa.customId, NVL(bi.companyName,bi.shopName) as companyName," +
				" sa.saleType, sa.status, spa.saleStatus," +
				" sa.onShelfTime, sa.offShelfTime, spa.expApproveTime, spa.approveTime," +
				" spa.approveUser,spa.remark," +
				" sa.isShare, sa.shareCode " +
				" from supply_area0 sa " +
				" left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag <> '0' and (spa.approveStatus = '1' or spa.approveStatus is null) " +
				" left join areaInfo ai on sa.areaId = ai.areaId" +
				" left join buyerInfo bi on sa.customId = bi.buyerId and bi.delflag <> '0' " +
				" where sa.supplyProductId = " + shopProductId +
				" and ((sa.saleStatus <> '1' and sa.status = '1') or (sa.saleStatus <> '1' and sa.status = '2') or (sa.saleStatus = '1') ) " +
				" and sa.del_flag = '1' and sa.isValId = '01' " +
				" order by sa.id ";
		return jdbcTemplate.query(sql, new ShopProductAreaVoMapper());
	}
	
	/**根据用户Id、销售类型(定向、非定向)  获得所有供货商产品Id列表（不分页,包括审核中的、已上架的、已下架的）   供剔除产品列表使用*/
	@Override
	public List<ShopProductVo> GetShopProductIdListNoPage(String userId,String saleType,String status) {
		String sql = " select sp.id, pm.productId " + 
					 " from shop_product sp " +
					 " inner join product_main pm on pm.productId = sp.productId and pm.del_flag = '1' " +
					 " inner join supply s on s.id =  sp.supplyId and s.del_flag = '1' " +
					 " inner join supply_area0 sa on sa.supplyProductId = sp.id and sa.del_flag = '1' and sa.isValId = '01' " +
					 " where s.user_id = '" + userId + "' " +
					 ((!"".equals(status) && status != null) ? " and (sa.saleStatus = '1' or (sa.status in ('1','2') and sa.saleStatus <> '1')) " : "") +
					 ((!"".equals(saleType) && saleType != null) ? " and sa.saleType in ('" + saleType + "') " : "") +
					 " and sp.del_flag = '1' and sp.isValId = '01' " +
					 " group by sp.id, pm.productId " +
					 " order by sp.id desc";
		
		logger.debug("查询供货产产品Id列表：" + sql);
		
		List<ShopProductVo> shopProductVoList = jdbcTemplate.query(sql, new RowMapper<ShopProductVo>(){

			@Override
			public ShopProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ShopProductVo shopProductVo = new ShopProductVo();
				shopProductVo.setId(rs.getBigDecimal("id"));
				shopProductVo.setProductId(rs.getBigDecimal("productId"));
				return shopProductVo;
			}
			
		});
		
		return shopProductVoList;
	}
	
	
	/**产品区域数据转换用*/
	private static final class ShopProductAreaVoMapper implements RowMapper<ShopProductAreaVo> {

		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setId(rs.getLong("id"));
			shopProductAreaVo.setAreaId(rs.getLong("areaId"));
			shopProductAreaVo.setAreaName(rs.getString("areaName"));
			shopProductAreaVo.setContinuous(rs.getBoolean("isContinuous"));
			shopProductAreaVo.setPrice(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setApprovePrice(rs.getBigDecimal("priceAfter"));
			shopProductAreaVo.setStockNum(rs.getLong("stockNumber"));
			shopProductAreaVo.setApproveStockNum(rs.getLong("stockAfter"));
			shopProductAreaVo.setCustomId(rs.getLong("customId"));
			shopProductAreaVo.setCustomName(rs.getString("companyName"));
			shopProductAreaVo.setSaleType(rs.getString("saleType"));
			shopProductAreaVo.setProductStatus(rs.getString("status"));
			shopProductAreaVo.setSaleStatus(rs.getString("saleStatus"));
			shopProductAreaVo.setOnShelfTime(rs.getDate("onShelfTime"));
			shopProductAreaVo.setOffShelfTime(rs.getDate("offShelfTime"));
			shopProductAreaVo.setExpApproveTime(rs.getDate("expApproveTime"));
			shopProductAreaVo.setApproveTime(rs.getDate("approveTime"));
			shopProductAreaVo.setApproveUser(rs.getLong("approveUser"));
			shopProductAreaVo.setRemark(rs.getString("remark"));
			shopProductAreaVo.setIsShare(rs.getString("isShare"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			return shopProductAreaVo;
		}
	}

	/**根据供货商产品信息提交产品审核申请*/
	@Override
	public int ApproveShopProductChange(ShopProductApproveVo shopProductApproveVo) {
		String insertSQL = " insert into shop_product_approve ( " +
				"  id " +
				" ,supplyAreaId " +
				" ,priceBefore " +
				" ,priceAfter " +
				" ,stockBefore " +
				" ,stockAfter " +
				" ,saleStatus " +
				" ,approveStatus " +
				" ,expApproveTime " +
				" ,del_flag " +
				" ,add_date " +
				" ,add_user " +
				" ,remark " +
				" ) " +
				"values ( SEQ_SHOP_PRODUCT_APPROVE_PK.NEXTVAL, ?, ?, ?, ?, ?, ?, '1', SYSDATE + 1, '1', SYSDATE, ?, ?) ";

		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[8];
			parmas[0] = shopProductAreaVo.getId();
			parmas[1] = shopProductAreaVo.getPrice();
			parmas[2] = shopProductAreaVo.getApprovePrice();
			parmas[3] = shopProductAreaVo.getStockNum();
			parmas[4] = shopProductAreaVo.getApproveStockNum();
			parmas[5] = shopProductAreaVo.getSaleStatus();
			parmas[6] = shopProductApproveVo.getShopProductVo().getAddUser();
			parmas[7] = shopProductAreaVo.getRemark();
			lstParams.add(parmas);
		}
		int[] i = jdbcTemplate.batchUpdate(insertSQL.toString(), lstParams);
		return i.length;
	}
	
	/**根据供货商产品信息提交产品审核申请*/
	@Override
	public int ApproveShopProductChange2(ShopProductApproveVo shopProductApproveVo) {

		String insertSQL = " insert into shop_product_approve ( " +
				"  id " +
				" ,supplyAreaId " +
				" ,priceBefore " +
				" ,priceAfter " +
				" ,stockBefore " +
				" ,stockAfter " +
				" ,saleStatus " +
				" ,approveStatus " +
				" ,expApproveTime " +
				" ,del_flag " +
				" ,add_date " +
				" ,add_user " +
				" ,remark " +
                " ,APPROVETIME " +
                " ,APPROVEUSER " +
				" ) " +
				" values ( ? , ?, ?, ?, ?, ?, ?, '3', SYSDATE + 1, '1', SYSDATE, ?, ?, SYSDATE, 1 )";
		// 天联在线的产品发布不需要审核即可使用，所以审核记录作为一个历史记录，默认审核通过，审核时间是申请的时间，审核人是测试中的admin，userid=1
		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[9];
			parmas[0] = shopProductApproveVo.getId()[i];
			parmas[1] = shopProductAreaVo.getId();
			parmas[2] = shopProductAreaVo.getPrice();
			parmas[3] = shopProductAreaVo.getApprovePrice();
			parmas[4] = shopProductAreaVo.getStockNum();
			parmas[5] = shopProductAreaVo.getApproveStockNum();
			parmas[6] = shopProductAreaVo.getSaleStatus();
			parmas[7] = shopProductApproveVo.getShopProductVo().getAddUser();
			parmas[8] = shopProductAreaVo.getRemark();
			lstParams.add(parmas);
		}
		
		int[] i = jdbcTemplate.batchUpdate(insertSQL.toString(), lstParams);
		return i.length;
	}

	/**根据供货商产品信息更新供应商产品状态*/
	@Override
	public int UpdateShopProductStatus(ShopProductApproveVo shopProductApproveVo) {

		String updateSQL = " update supply_area0 set saleStatus = '1' where id = ? ";
		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[1];
			parmas[0] = shopProductAreaVo.getId();
			lstParams.add(parmas);
		}
		int[] i = jdbcTemplate.batchUpdate(updateSQL.toString(), lstParams);
		return i.length;
	}
	
	/**根据供货商产品信息更新供应商产品状态*/
	@Override
	public int UpdateShopBidProductStatus(ShopProductApproveVo shopProductApproveVo) {

		String updateSQL = " update supply_area0 set saleStatus = '1',createTime = sysdate where id = ? ";

		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[1];
			parmas[0] = shopProductAreaVo.getId();
			lstParams.add(parmas);
		}
		int[] i = jdbcTemplate.batchUpdate(updateSQL.toString(), lstParams);
		return i.length;
	}
	
	/**根据供应商产品信息更新供应商产品继续竞价状态*/
	@Override
	public int UpdateContinuous(ShopProductAreaVo shopProductAreaVo) {
		String updateSQL = " update supply_area0 set isContinuous = ? where id = ? ";
		Object[] parmas = new Object[2];
		parmas[0] = shopProductAreaVo.isContinuous() ? "0" : "1";
		parmas[1] = shopProductAreaVo.getId();
		
		int i = jdbcTemplate.update(updateSQL.toString(), parmas);
		return i;
	}
	
	/**根据用户Id、销售类型(定向、非定向)、型号名称、供货商产品Id  获得审核中的产品列表数量*/
	@Override
	public int GetApproveShopProductCountByProId(String userId, String saleType, String modelName, String shopProductId) {
        List<String> conditionList = new ArrayList<String>();
		
		String sql = " select count(1) " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" where d.user_id = '" + userId + "'" +
				" and EXISTS (select 1 from supply_area0 e where a.id = e.supplyProductId and e.supplyproductid= '" + shopProductId + "'" +
				" and e.del_flag = '1' and e.isValId = '01' and e.saleStatus = '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" ) " +
				" and a.del_flag = '1' and a.isValId = '01' ";
		
		if(modelName != null && !"".equals(modelName)){
			sql += " and pm.modelName like ? ";
			conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
		}
		
		Object[] params = conditionList.toArray();
		return jdbcTemplate.queryForInt(sql,params);
	}
	
	/**根据用户Id、销售类型(定向、非定向)、上下架状态、型号名称、供货商产品Id 获得所有供货商产品列表数量*/
	@Override
	public int GetShopProductCountByProId(String userId, String saleType,String status, String modelName, String shopProductId) {
		List<String> conditionList = new ArrayList<String>();
		String sql = " select count(1) " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" where a.id not in (select distinct bsp.supplyproductid from bidding_sp bsp " +
				" inner join biddingm bm on bm.id=bsp.pid and bm.statue in ('1','2') " +
				" where bsp.product_statu in ('1','2','3','4','6') and bsp.supplyproductid is not null) " + 
				" and d.user_id = '" + userId + "'" +
				" and EXISTS (select 1 from supply_area0 e where a.id = e.supplyProductId  and e.supplyproductid= '" + shopProductId + "'" +
				" and e.del_flag = '1' and e.isValId = '01' and e.status = '" + status + "' and e.saleStatus <> '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" ) " +
				" and a.del_flag = '1' and a.isValId = '01' ";
		
		if(modelName != null && !"".equals(modelName)){
			sql += " and pm.modelName like ? ";
			conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
		}
		
		Object[] params = conditionList.toArray();
		
		return jdbcTemplate.queryForInt(sql,params);
	}

}
